C:/Projekte/RAIS/dataaccesslayertest/TestUtilities.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using NUnit.Framework;
using RAIS.Common.DynamicMaskManagement;
using RAIS.Common.Localization;
using RAIS.Common.TableManagement;
using RAIS.Common.UserManagement;
using RAIS.DataAccessLayer;
using Cultures = System.Collections.Generic.Dictionary<string, RAIS.Common.Localization.Culture>;
using TableGroups = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.TableGroup>;
using Tables = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Table>;

namespace RAIS.DataAccessLayerTest
{
        public static class TestUtilities
        {
                #region Create Objects Utilities
                public static FunctionRole NewFunctionRoleObject(int i)
                {
                        FunctionRole functionRole = new FunctionRole();
                        functionRole.Name = "Test Functional Role " + i.ToString();
                        functionRole.Validation = FunctionRole.ValidationType.NeedsValidation;

                        return functionRole;
                }

                public static DataRole NewDataRoleObject(int i)
                {
                        DataRole dataRole = new DataRole();
                        dataRole.Name = "Test Data Role " + i.ToString();

                        return dataRole;
                }

                public static UserData NewUserObject(int i, FunctionRole functionRole, DataRole dataRole)
                {
                        /*UserData user = new UserData();
                        user.Name = "Test Complete Name " + i.ToString();
                        user.Login = "testLogin" + i.ToString();
                        user.Password = "testPassword" + i.ToString();
                        user.CompleteName = user.Name;
                        user.Email = "test@email" + i.ToString() + ".com";
                        user.Locked = false;
                        user.TypeText = "Public Body";
                        user.FunctionalRole = functionRole;
                        user.DataRole = dataRole;
                        user.DataRoleType = (int)UserData.DataRoleIndication.DataRole;
            */
                        return null;
                }

                public static FunctionRoleAttribute NewTestFunctionRoleAttribute(int i)
                {
                        FunctionRoleAttribute functionRoleAttribute = new FunctionRoleAttribute();
                        functionRoleAttribute.Category = NewTestCategoryWithSave(1);
                        functionRoleAttribute.Subcategory = NewTestSubcategoryWithSave(1, functionRoleAttribute.Category);
                        functionRoleAttribute.Mask = NewTestMaskWithSave(1, functionRoleAttribute.Category, functionRoleAttribute.Subcategory);

                        return functionRoleAttribute;
                }

                public static FunctionRole NewTestFunctionRoleWithSave(int i)
                {
                        bool returnStatus;
                        string errorMessage = "";

                        FunctionRole expectedFunctionRole = TestUtilities.NewFunctionRoleObject(i);
                        returnStatus = UserManagement.AddFunctionRole(expectedFunctionRole.Name, ref errorMessage);
                        if (!returnStatus)
                                throw new Exception("Add Functional Role Error");
                        expectedFunctionRole = UserManagement.FunctionalRoles[expectedFunctionRole.Name];

                        return expectedFunctionRole;
                }

                public static DataRoleAttribute NewTestDataRoleAttribute(int i)
                {
                        DataRoleAttribute dataRoleAttribute = new DataRoleAttribute();
                        dataRoleAttribute.Region = NewTestRegionWithSave(1);
                        dataRoleAttribute.District = NewTestDistrictWithSave(1, dataRoleAttribute.Region);
                        dataRoleAttribute.Facility = NewTestFacilityWithSave(1, dataRoleAttribute.Region, dataRoleAttribute.District);

                        return dataRoleAttribute;
                }

                public static DataRole NewTestDataRoleWithSave(int i)
                {
                        bool returnStatus;
                        string errorMessage = "";

                        DataRole expectedDataRole = TestUtilities.NewDataRoleObject(i);
                        returnStatus = UserManagement.AddDataRole(expectedDataRole.Name, ref errorMessage);
                        if (!returnStatus)
                                throw new Exception("Add Data Role Error");
                        expectedDataRole = UserManagement.DataRoles[expectedDataRole.Name];

                        return expectedDataRole;
                }

                public static UserData NewTestUser(int i)
                {
                        FunctionRole expectedFunctionRole = NewTestFunctionRoleWithSave(i);

                        DataRole expectedDataRole = NewTestDataRoleWithSave(i);

                        UserData expectedUser = TestUtilities.NewUserObject(i, expectedFunctionRole, expectedDataRole);

                        return expectedUser;
                }

                public static UserData NewTestUserWithSave(int i)
                {
                        bool returnStatus;
                        string errorMessage = "";

                        UserData expectedUser = NewTestUser(i);

                        returnStatus = UserManagement.AddUser(expectedUser, ref errorMessage);
                        if (!returnStatus)
                                throw new Exception("Add User Error");

                        return expectedUser;
                }

                public static Region NewTestRegionWithSave(int i)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string regionName = "Test Region " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [Region] ([Region Name]) VALUES ('" + regionName + "')"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                Region region = UserManagement.Regions[regionName];

                                return region;
                        }
                }

                public static District NewTestDistrictWithSave(int i, Region region)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string districtName = "Test District " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [District] ([FK Region ID], [District Name]) VALUES ("
                                                + region.ID.ToString()
                                                + ", '" + districtName + "')"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                District district = UserManagement.GetDistrictsByRegion(region)[districtName];

                                return district;
                        }
                }

                public static Facility NewTestFacilityWithSave(int i, Region region, District district)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string facilityName = "Test Facility " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [Facility] "
                                                + "([Regulatory Authority Number], [Facility Name], [FK Facility Status ID], [FK Region ID], [FK District ID])"
                                                + " SELECT "
                                                + "'[Unknown]'"
                                                + ", '" + facilityName + "'"
                                                + ", [PK Facility Status ID]"
                                                + ", " + region.ID.ToString()
                                                + ", " + district.ID.ToString()
                                                + " FROM [Facility Status] WHERE [Status Name] = '[Unknown]'"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                Facility facility = UserManagement.GetFacilitiesByRegionAndDistrict(region, district)[facilityName];

                                return facility;
                        }
                }

                public static FunctionRole.Category NewTestCategoryWithSave(int i)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string categoryName = "Test Category " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [Category] ([Category Name], [Order]) VALUES ('" + categoryName + "', " + i.ToString() + ")"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                FunctionRole.Category category = UserManagement.FunctionalRoleCategories[categoryName];

                                return category;
                        }
                }

                public static FunctionRole.Subcategory NewTestSubcategoryWithSave(int i, FunctionRole.Category category)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string subcategoryName = "Test Subcategory " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [Subcategory] ([FK Category ID], [Subcategory Name], [Order], [Image Index]) VALUES ("
                                                + category.ID.ToString()
                                                + ", '" + subcategoryName + "'"
                                                + ", " + i.ToString()
                                                + ", 0"
                                                + ")"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                FunctionRole.Subcategory subcategory = UserManagement.GetFunctionalRoleSubcategories(category)[subcategoryName];

                                return subcategory;
                        }
                }

                public static Mask NewTestMaskWithSave(int i, FunctionRole.Category category, FunctionRole.Subcategory subcategory)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                string maskName = "Test Mask " + i.ToString();

                                sqlConnection.Open();

                                using (SqlCommand sqlCommand =
                                        new SqlCommand("INSERT [Mask] ("
                                                + "[Mask Name]"
                                                + ", [FK Subcategory ID]"
                                                + ", [Order]"
                                                + ", [Image Index]"
                                                + ", [Reference]"
                                                + ") VALUES ("
                                                + "'" + maskName + "'"
                                                + ", " + subcategory.ID.ToString()
                                                + ", " + i.ToString()
                                                + ", 0"
                                                + ", '" + maskName + ".aspx'"
                                                + ")"
                                        , sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.Text;
                                        sqlCommand.ExecuteNonQuery();
                                }

                                Mask mask = UserManagement.GetMasks(category, subcategory)[maskName];

                                return mask;
                        }
                }

                public static Culture NewTestCultureWithSave(int i)
                {
                        bool returnStatus;
                        string errorMessage = "";

                        Culture expectedCulture = new Culture(i);
                        returnStatus = Localisation.AddCulture(expectedCulture, ref errorMessage);
                        if (!returnStatus)
                                throw new Exception("Add culture error");

                        return expectedCulture;
                }

        public static Table NewTableObject(int i)
        {
            Table table = new Table();

            table.Name = "Test Table " + i.ToString();
            table.TypeText = "Single";
            table.CustomTable = true;

            return table;
        }

        public static Table NewTestTableWithSave(int i)
        {
          /*  string errorMessage = "";
            string tableGroupName = "Associated Equipments";

            TableGroups tableGroups = TableManagement.TableGroups;
            TableGroup tableGroup = tableGroups[tableGroupName];

            Table table = NewTableObject(i);

            if (!TableManagement.AddTable(table, tableGroup, ref errorMessage))
                throw new Exception("Add Table Error");*/

            return null;
        }

        public static Field NewTextFieldObject(int i)
        {
            return NewFieldObject(i, "Text");
        }

        public static Field NewSingleLookupFieldObject(int i)
        {
            return NewFieldObject(i, "Single Lookup");
        }

        public static Field NewLookupPlusIntegerFieldObject(int i)
        {
            return NewFieldObject(i, "Lookup Plus Integer Value");
        }

        public static Field NewFieldObject(int i, string typeText)
        {
            Field field = new Field();
            field.Name = "Test Field " + i.ToString();
            field.FieldPurpose = "Test Field Purpose";
            field.LocalLanguageFieldName = "Òåñòîâîå ïîëå " + i.ToString();
            field.LocalLanguageFieldPurpose = "Íàçíà÷åíèå òåñòîâîãî ïîëÿ";
            field.TypeText = typeText;
            field.NecessityText = "Required";
            field.Order = i;
            field.IsNameField = false;
            field.SystemField = false;
            field.VisibleOnForms = true;
            field.UniqueValue = false;
            field.NeedsTranslation = false;
            field.Multiline = false;
            field.RelatedTable = null;

            return field;
        }

        public static Table CreateSingleTable(int i, TableGroup tableGroup)
        {
           /* string errorMessage = "";
            Table table = new Table();

            table.Name = "_Test Table " + i.ToString();
            table.TypeText = "Single";
            table.CustomTable = true;
            Assert.IsTrue(TableManagement.AddTable(table, tableGroup, ref errorMessage)
                , string.Format("Add table {0}", table.Name));
*/
            return null;
        }

        public static string TestFieldName(int i)
        {
            return "Test Field " + i.ToString();
        }

        public static Field AddTextField(int i, Table table)
        {
            string errorMessage = "";
            Field field = new Field();

            field.Name = TestFieldName(i);
            field.LocalLanguageFieldName = "Ïîëå " + i.ToString();
            field.FieldPurpose = "Field Purpouse";
            field.LocalLanguageFieldPurpose = "Íàçíà÷åíèå ïîëÿ";
            field.TypeText = "Text";
            field.SizeText = "Small (below 50 chars)";
            field.NecessityText = "Required";
            field.Order = i;
            field.IsNameField = true;
            field.SystemField = false;
            field.VisibleOnForms = true;
            field.UniqueValue = true;
            field.NeedsTranslation = false;
            field.Multiline = false;
            field.RelatedTable = null;
            Assert.IsTrue(TableManagement.AddField(table, field, ref errorMessage)
                , string.Format("Add field {0} to table {1}", field.Name, table.Name));

            if (table.Fields == null)
                table.Fields = new Dictionary<string, Field>();

            table.Fields.Add(field.Name, field);

            return field;
        }

        public static Field AddSingleLookupField(int i, Table table, Table relatedTable)
        {
            string errorMessage = "";
            Field field = new Field();

            field.Name = "Test Field " + i.ToString();
            field.LocalLanguageFieldName = "Ïîëå " + i.ToString();
            field.FieldPurpose = "Field Purpouse" + i.ToString();
            field.LocalLanguageFieldPurpose = "Íàçíà÷åíèå ïîëÿ" + i.ToString();
            field.TypeText = "Single Lookup";
            field.NecessityText = "Suggested";
            field.Order = i;
            field.IsNameField = false;
            field.SystemField = false;
            field.VisibleOnForms = true;
            field.UniqueValue = false;
            field.NeedsTranslation = false;
            field.Multiline = false;
            field.RelatedTable = relatedTable;
            Assert.IsTrue(TableManagement.AddField(table, field, ref errorMessage)
                , string.Format("Add field {0} to table {1}", field.Name, table.Name));

            return field;
        }

        public static Field AddLookupPlusIntegerField(int i, Table table, Table relatedTable)
        {
            string errorMessage = "";
            Field field = new Field();

            field.Name = "Test Field " + i.ToString();
            field.LocalLanguageFieldName = "Ïîëå " + i.ToString();
            field.FieldPurpose = "Field Purpouse" + i.ToString();
            field.LocalLanguageFieldPurpose = "Íàçíà÷åíèå ïîëÿ" + i.ToString();
            field.TypeText = "Lookup Plus Integer Value";
            field.NecessityText = "Required";
            field.Order = i;
            field.IsNameField = false;
            field.SystemField = false;
            field.VisibleOnForms = true;
            field.UniqueValue = false;
            field.NeedsTranslation = false;
            field.Multiline = false;
            field.RelatedTable = relatedTable;
            Assert.IsTrue(TableManagement.AddField(table, field, ref errorMessage)
                , string.Format("Add field {0} to table {1}", field.Name, table.Name));

            return field;
        }

        public static DynamicMask CreateSingleRowMaskWithListInDB(int i, Table table)
        {
            DynamicMask dynamicMask = new DynamicMask();

            dynamicMask.Name = "Test Dynamic Mask " + i.ToString();
            dynamicMask.Type = DynamicMask.DynamicMaskType.SingleRowMaskWithList;
            dynamicMask.Table = table;

            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand =
                    new SqlCommand(string.Format(
                            "INSERT [Dynamic Mask]"
                            + " ([Dynamic Mask Name], [FK Dynamic Mask Type ID], [FK Table ID])"
                            + " VALUES"
                            + " ('{0}', {1}, {2})"
                            , dynamicMask.Name, dynamicMask.TypeData.ID, dynamicMask.Table.ID)
                        , sqlConnection))
                {
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    try
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Add DynamicMask Error: " + ex.Message);
                    }
                }
            }
            
            return dynamicMask;
        }
                #endregion

                #region Cleaning Utilities
                //public bool DeleteUser(UserData user)
                //{
                //    try
                //    {
                //        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                //        {
                //            sqlConnection.Open();

                //            using (SqlCommand sqlCommand =
                //                new SqlCommand("DELETE FROM [User] WHERE [User Name] = '" + user.Login + "'"
                //                , sqlConnection))
                //            {
                //                sqlCommand.CommandType = System.Data.CommandType.Text;
                //                sqlCommand.ExecuteNonQuery();
                //            }
                //        }
                //        return true;
                //    }
                //    catch
                //    {
                //        return false;
                //    }
                //}

                public static bool TruncateTable(string tableName)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [" + tableName + "]"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableRegion()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [Region] WHERE [PK Region ID] != dbo.ufn_GetAllRegionsID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableDistrict()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [District] WHERE [PK District ID] != dbo.ufn_GetAllDistrictsID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableFacility()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [Facility] WHERE [PK Facility ID] != dbo.ufn_GetAllFacilitiesID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableCategory()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [Category] WHERE [PK Category ID] != dbo.ufn_GetAllCategoriesID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableSubcategory()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [Subcategory] WHERE [PK Subcategory ID] != dbo.ufn_GetAllSubcategoriesID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTableMask()
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand =
                                                new SqlCommand("DELETE FROM [Mask] WHERE [PK Mask ID] != dbo.ufn_GetAllMasksID()"
                                                , sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.Text;
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                return true;
                        }
                        catch
                        {
                                return false;
                        }
                }

                public static bool TruncateTables()
                {
                        bool returnCode = true;

                        returnCode &= TruncateTable("User");

                        returnCode &= TruncateTable("Functional Role Restriction");
                        returnCode &= TruncateTable("Functional Role");

                        returnCode &= TruncateTable("Data Role Restriction");
                        returnCode &= TruncateTable("Data Role");

                        returnCode &= TruncateTableFacility();
                        returnCode &= TruncateTableDistrict();
                        returnCode &= TruncateTableRegion();

                        returnCode &= TruncateTableMask();
                        returnCode &= TruncateTableSubcategory();
                        returnCode &= TruncateTableCategory();

                        return returnCode;
                }

                public static bool DeleteTestCultures()
                {
                        bool returnStatus = true;
                        string errorMessage = "";

                        Cultures cultures = Localisation.SupportedLanguages;

                        foreach (KeyValuePair<string, Culture> culturePair in cultures)
                                if (culturePair.Key != "English")
                                        returnStatus &= Localisation.DeleteCulture(culturePair.Value, ref errorMessage);

                        return returnStatus;
                }

        public static bool DeleteTestTables()
        {
           /* string errorMessage = "";
            bool returnCode = true;

            Tables tables = TableManagement.GetTablesByTableGroup(TableManagement.TableGroups["Associated Equipments"]);

            foreach (KeyValuePair<string, Table> kvp in tables)
            {
                Table table = kvp.Value;
                if (table.CustomTable)
                    returnCode &= TableManagement.RemoveTable(table, ref errorMessage);
            }
            */
            return true;
        }

        public static bool TruncateTablesForDynamicMaskManagementTest()
        {
            bool returnCode = true;

            // Delete test Tables
            returnCode &= DeleteTestTables();

            // Delete Test Dynamic Masks
            returnCode &= TruncateTable("Dynamic Mask");

            return returnCode;
        }

                #endregion
        }
}